package com.aaa.Dao;

import com.aaa.Entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private RowMapper<User> rowMapper = new RowMapper<User>() {
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            User user = new User();
            user.setId(resultSet.getLong("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            return user;
        }
    };

    public User findUAndP(User user){
            String sql ="select id,username,password from users where username=? and password =?";
            List<User> query = jdbcTemplate.query(sql, rowMapper, user.getUsername(), user.getPassword());
            if(query.size()>0){
                return query.get(0);
            }else {
            return null;
        }
    }
    public List<User> listPage(String keyword,Integer offset,Integer limit){
        String sql="select id,username,password from users where username like ? order by id desc limit ?,?";

        List<User> result = this.jdbcTemplate.query(sql, rowMapper, "%" + keyword + "%", offset, limit);
        return result;
    }
    //查询总条数
    public Integer count(String keyword){
        String sql="select count(id) as c from users where username like ?";
        Integer result = this.jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {

            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        },"%"+keyword+"%");
        return result;
    }
    public Integer save(User user){
        String sql="insert into users (username,password) values(?,?)";
        int count = this.jdbcTemplate.update(sql, user.getUsername(), user.getPassword());
        return count;
    }
    public Integer update(User user){
        String sql="update users set username=?,password=? where id=?";
        int count = this.jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getId());
        return count;
    }
    public Integer delete(Long id){
        String sql="delete from users where id=?";
        int count = this.jdbcTemplate.update(sql, id);
        return count;
    }
}
